
Imports System.Data.SqlClient
Imports AVLProject.DeclaracionesFunciones

Public Class GeoPoint

    Private mMovil As Integer
    Private mFecha As String
    Private mHoraUTC As String
    Private mLatitud As String
    Private mLongitud As String
    Private mVelocidad As String


    Public Sub New()
        mMovil = IDMovil
        mFecha = ""
        mHoraUTC = ""
        mLatitud = ""
        mLongitud = ""
        mVelocidad = ""
    End Sub

    Public Property Fecha() As String
        Get
            Return mFecha
        End Get
        Set(ByVal value As String)
            mFecha = value
        End Set
    End Property


    Public Property IDMovil() As Integer
        Get
            Return mMovil
        End Get
        Set(ByVal value As Integer)
            mMovil = value
        End Set
    End Property


    Public Property HoraUTC()
        Get
            Return mHoraUTC
        End Get
        Set(ByVal value)
            mHoraUTC = value
        End Set
    End Property

    Public Property Latitud() As String
        Get
            Return mLatitud
        End Get
        Set(ByVal value As String)
            mLatitud = value
        End Set
    End Property

    Public Property Longitud() As String
        Get
            Return mLongitud
        End Get
        Set(ByVal value As String)
            mLongitud = value
        End Set
    End Property

    Public Property Velocidad() As String
        Get
            Return mVelocidad
        End Get
        Set(ByVal value As String)
            mVelocidad = value
        End Set
    End Property




    Public Function Grabar(ByVal IDMovil As Integer, ByVal Fecha As String, ByVal HoraUTM As String, ByVal Latitud As String, ByVal Longitud As String, ByVal Velocidad As String) As Boolean

        Try

            'Dim StringConecta As String = "User Id=sa2;password=1234;Data Source=127.0.0.1\SQLEXPRESS;Initial Catalog=TEST"
            Dim Conecta As New SqlConnection(StringConecta)
            Conecta.Open()

            Comando = New SqlCommand(StringComando, Conecta)
            Comando.CommandText = "AddPointV2"
            Comando.CommandType = CommandType.StoredProcedure
            Comando.Parameters.Add("@IDMOVIL", SqlDbType.Int, 4, "IDMOVIL").Value = IDMovil
            Comando.Parameters.Add("@FECHA", SqlDbType.VarChar, 8, "FECHA").Value = FormatoFechaHora(HoraUTM, Fecha).ToString("dd/MM/yy")
            Comando.Parameters.Add("@HORA", SqlDbType.VarChar, 8, "HORA").Value = FormatoFechaHora(HoraUTM, Fecha).ToString("HH:mm:ss")
            Comando.Parameters.Add("@LATITUD", SqlDbType.VarChar, 9, "LATITUD").Value = Trim(FormatoDecimal(Latitud))
            Comando.Parameters.Add("@LONGITUD", SqlDbType.VarChar, 9, "LONGITUD").Value = Trim(FormatoDecimal(Longitud))
            Comando.Parameters.Add("@VELOCIDAD", SqlDbType.VarChar, 5, "VELOCIDAD").Value = Trim(NudosToKilometrosHora(Velocidad))

            Comando.ExecuteNonQuery()

            Return True
        Catch ex As Exception

            MsgBox(ex.ToString & vbCrLf & Fecha & vbCrLf & HoraUTM & vbCrLf & Latitud & vbCrLf & Longitud & vbCrLf & Velocidad)
            Return False
        End Try

    End Function
    
    Public Function ReadLastPointsUpdated() As DataSet

        Try

            'Dim StringConecta As String = "User Id=sa2;password=1234;Data Source=127.0.0.1\SQLEXPRESS;Initial Catalog=TEST"
            Dim Conecta As New SqlConnection(StringConecta)
            Conecta.Open()

            Dim Adapter As New SqlDataAdapter
            Dim Ds As New DataSet

            StringComando = "SELECT TOP 100 * FROM GPSV2 ORDER BY FECHA DESC, HORA DESC "

            Comando = New SqlCommand(StringComando, Conecta)

            Adapter.SelectCommand = Comando
            Adapter.Fill(Ds, "ListarPuntos")

            Return Ds

        Catch ex As Exception

            Return Ds

        End Try


    End Function


    Public Function LastPointUpdated() As GeoPoint

        Try

            'Dim StringConecta As String = "User Id=sa2;password=1234;Data Source=127.0.0.1\SQLEXPRESS;Initial Catalog=TEST"
            Dim Conecta As New SqlConnection(StringConecta)
            Conecta.Open()

            Dim Adapter As New SqlDataAdapter
            Dim Ds As New DataSet

            StringComando = "SELECT TOP 1 * FROM GPSV2 ORDER BY FECHA DESC, HORA DESC"


            Comando = New SqlCommand(StringComando, Conecta)

            Adapter.SelectCommand = Comando
            Adapter.Fill(Ds, "Punto")

            Dim Punto As New GeoPoint

            Punto.IDMovil = IDMovil
            Punto.Fecha = Ds.Tables(0).Rows(0).Item(1).ToString
            Punto.HoraUTC = Ds.Tables(0).Rows(0).Item(2).ToString
            Punto.Latitud = Ds.Tables(0).Rows(0).Item(3).ToString
            Punto.longitud = Ds.Tables(0).Rows(0).Item(4).ToString

            Return Punto

        Catch ex As Exception

            Dim Punto As New GeoPoint
            Return Punto

        End Try


    End Function
    


    Public Function FillPointsInWayPoint(ByVal FechaConsulta As String, _
                                                    ByVal HoraMin As String, _
                                                    ByVal HoraMax As String _
                                                    ) As List(Of GeoPoint)

        Try

            'Dim StringConecta As String = "User Id=sa2;password=1234;Data Source=127.0.0.1\SQLEXPRESS;Initial Catalog=TEST"
            Dim Conecta1 As New SqlConnection(StringConecta)
            Conecta1.Open()

            Dim Adapter1 As New SqlDataAdapter
            Dim Ds1 As New DataSet

            Dim StringComando1 As String

            StringComando1 = "SELECT * FROM GPSV2 "
            StringComando1 += "WHERE FECHA ='" & FechaConsulta & "' AND "
            StringComando1 += "HORA >= '" & HoraMin & "' AND HORA <= '" & HoraMax & "'"

            Dim Comando1 = New SqlCommand(StringComando1, Conecta1)

            Adapter1.SelectCommand = Comando1
            Adapter1.Fill(Ds1, "PuntosW")

            Dim Punto As New GeoPoint
            Dim WayPoints As New List(Of GeoPoint)


            Dim Consulta As DataTable = Ds.Tables("Puntos")
            Dim Fila As DataRow

            For Each Fila In Consulta.Rows

                Punto.IDMovil = Fila(0)
                Punto.Fecha = Fila(1)
                Punto.HoraUTC = Fila(2)
                Punto.Latitud = Fila(3)
                Punto.Longitud = Fila(4)
                Punto.Velocidad = Fila(5)

                WayPoints.Add(Punto)

            Next

            Return WayPoints

        Catch ex As Exception

            Dim WayPoints As New List(Of GeoPoint)
            Return WayPoints

        End Try

    End Function

End Class